
//-------------------------------
// TABLE A4
//-------------------------------

clear
use masterdata_policing.dta

gen vcrime_tot = murders + agg_assault + forc_rape + robbery
gen vcrime_clear = murders_clear + agg_assault_clear + forc_rape_clear + robbery_clear
gen vcrimeclearrate = vcrime_clear/vcrime_tot
gen pcrime_tot = burglary + larcenytheft + mvt
gen pcrime_clear = burglary_clear + larceny_clear + mvt_clear
gen pcrimeclearrate = pcrime_clear/pcrime_tot


gen youthratio = (pop15to17+pop18to24+pop25to34)/totalpopulation
gen blackratio = black/totalpopulation
gen lessthanhighratio = lessthanhigh/popover25
gen unempratio = unemployed/totalpopulation

encode state, gen(state2)
by fipsstate fipscounty, sort: gen countyid = 1 if _n == 1
replace countyid = sum(countyid)

gen lpop = ln(totalpopulation +1)
gen lincome = ln(medianincome + 1)

gen polbudgetpc = totalbudget/population
gen totpolemppc =(fullswornofficer + fullcivilianemp + ptimeswornofficer + ptimecivilianemp)/population
gen swpolemppc = (fullswornofficer + ptimeswornofficer)/population
gen swfullpolemppc = fullswornofficer/population 

// Change variable unit as %
replace vcrimeclearrate = vcrimeclearrate*100
replace pcrimeclearrate = pcrimeclearrate*100
replace policebudget = policebudget*100
replace youthratio = youthratio*100
replace blackratio = blackratio*100
replace lessthanhighratio = lessthanhighratio*100
replace unempratio = unempratio*100
replace gini = gini*100

macro define demographic "lpop youthratio blackratio lessthanhighratio unempratio gini lincome"

tabstat $crime $finance2 $demographic, stat(N mean sd min max) col(stat)

gen lvcrime_tot = ln(vcrime_tot +1)
gen lpcrime_tot = ln(pcrime_tot+1)
gen lvcrime_clear = ln(vcrime_clear +1)
gen lpcrime_clear = ln(pcrime_clear+1)

// (b) drop gang cities

drop if city =="CHICAGO"
drop if city =="LOS ANGELES"
drop if city =="PHILADELPHIA" & state =="PA"
drop if city =="MIAMI" & state =="FL"
drop if city =="NEW YORK"
drop if city =="ATLANTA" & state == "GA"
drop if city =="CLEVELAND" & state == "OH"
drop if city =="ST LOUIS" & state == "MO"
drop if city =="DALLAS" & state == "TX"
drop if city =="MEMPHIS" 
drop if city =="NASHVILLE" & state == "TN"
drop if city =="NEW ORLEANS"
drop if city =="MILWAUKEE"
drop if city =="ORLANDO"
drop if city =="HOUSTON" & state == "TX"
drop if city =="DETROIT"

* Regression

eststo clear
eststo: quietly reg  vcrimeclearrate pctfinesandforfeits i.year, vce(cluster geo_fips)
eststo: quietly areg vcrimeclearrate pctfinesandforfeits lvcrime_tot lpcrime_tot policebudget swfullpolemppc $demographic i.year if population <= 28010, a(countyid) vce(cluster geo_fips)
eststo: quietly areg vcrimeclearrate pctfinesandforfeits lvcrime_tot lpcrime_tot policebudget swfullpolemppc $demographic i.year if population > 28010, a(countyid) vce(cluster geo_fips)
eststo: quietly reg  pcrimeclearrate pctfinesandforfeits i.year, vce(cluster geo_fips)
eststo: quietly areg pcrimeclearrate pctfinesandforfeits lvcrime_tot lpcrime_tot policebudget swfullpolemppc $demographic i.year, a(countyid) vce(cluster geo_fips)
esttab, star(** 0.05 *** 0.01) ar2
esttab using countyfe_gangcityexcluded2.tex, star(** 0.05 *** 0.01) ar2 replace



